# insert_mysql_tool.py
import mysql.connector
from mysql.connector import Error
from .reding_mysql_tool import get_db_config


class DataStore:
    def __init__(self, sorted_data):
        self.sorted_data = sorted_data
        self.book_id = sorted_data.get('book_id', '')
        self.title = sorted_data.get('title', '')
        self.author = sorted_data.get('author', '')
        self.page_id = sorted_data.get('page_id', '')
        self.page_number = sorted_data.get('page_number', 0)
        self.total_lines = sorted_data.get('total_lines', 0)
        self.layout_direction = sorted_data.get('layout_direction', 0)
        self.reading_sequence = sorted_data.get('reading_sequence', 0)
        self.ocr_source_images = sorted_data.get('ocr_source_images', '')
        self.page_image_path = sorted_data.get('page_image_path', '')
        self.page_word = sorted_data.get('page_word', [])

    def save_sql_data(self):
        """将DataStore中的数据写入数据库"""
        connection = None
        try:
            # 获取数据库配置
            db_config = get_db_config()

            # 连接数据库
            connection = mysql.connector.connect(**db_config)

            if connection.is_connected():
                cursor = connection.cursor()

                # 开始事务
                connection.start_transaction()

                # 1. 插入书籍信息
                book_id = self.insert_pub_books(cursor)

                # 2. 插入页面信息
                page_id = self.insert_pub_pages(cursor)

                # 3. 插入行和字符信息
                for line_data in self.page_word:
                    line_id = line_data.get('line_id', 0)
                    word_info_line = line_data.get('word_info', [])
                    section_side = line_data.get('section_side', 0)
                    char_count = line_data.get('char_count', 0)
                    full_text = line_data.get('full_text', '')

                    if word_info_line:
                        # 插入行信息
                        pub_text_lines_id = self.insert_pub_text_lines(cursor, line_id, section_side, char_count,
                                                                       full_text, word_info_line)

                        # 插入字符信息
                        self.insert_pub_characters(cursor, word_info_line, pub_text_lines_id, section_side)

                # 提交事务
                connection.commit()
                print(f"数据插入成功 - 书籍: {self.title}, 页面: {self.page_id}")

        except Error as e:
            print(f"数据库错误: {e}")
            if connection:
                connection.rollback()
            raise e
        finally:
            if connection and connection.is_connected():
                cursor.close()
                connection.close()

    def insert_pub_books(self, cursor):
        """往pub_books插入基础信息"""
        try:
            # 检查书籍是否存在
            check_sql = "SELECT id FROM pub_books WHERE id = %s"
            cursor.execute(check_sql, (self.book_id,))
            result = cursor.fetchone()

            if result:
                print(f"书籍已存在: {self.book_id}")
                return result[0]
            else:
                insert_sql = """
                INSERT INTO pub_books (id, title, author, layout_direction, ocr_source_images, created_at, updated_at)
                VALUES (%s, %s, %s, %s, %s, NOW(), NOW())
                """
                cursor.execute(insert_sql, (
                    self.book_id, self.title, self.author, self.layout_direction, self.ocr_source_images
                ))
                print(f"插入新书籍: {self.book_id}")
                return self.book_id

        except Error as e:
            print(f"插入书籍信息错误: {e}")
            raise

    def insert_pub_pages(self, cursor):
        """往pub_pages插入基础信息"""
        try:
            # 检查页面是否存在
            check_sql = "SELECT id FROM pub_pages WHERE id = %s"
            cursor.execute(check_sql, (self.page_id,))
            result = cursor.fetchone()

            if result:
                print(f"页面已存在: {self.page_id}")
                return result[0]
            else:
                insert_sql = """
                INSERT INTO pub_pages (id, book_id, page_number, ocr_source_image, text_direction, total_lines, created_at, updated_at)
                VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW())
                """
                cursor.execute(insert_sql, (
                    self.page_id, self.book_id, self.page_number, self.page_image_path,
                    self.layout_direction, self.total_lines
                ))
                print(f"插入新页面: {self.page_id}")
                return self.page_id

        except Error as e:
            print(f"插入页面信息错误: {e}")
            raise

    def insert_pub_text_lines(self, cursor, line_id, section_side, char_count, full_text, word_info_line):
        """往pub_text_lines插入基本行信息"""
        try:
            # 检查行是否已存在
            check_sql = "SELECT id FROM pub_text_lines WHERE page_id = %s AND line_number = %s"
            cursor.execute(check_sql, (self.page_id, line_id))
            result = cursor.fetchone()

            if result:
                pub_text_lines_id = result[0]
                print(f"文本行已存在: 页面 {self.page_id}, 行 {line_id}")
                # 更新数据
                update_sql = """
                UPDATE pub_text_lines SET page_id=%s,line_number=%s,text_direction=%s,reading_sequence=%s,char_count=%s,full_text =%s,line_order=%s,section_side=%s,sentence_image_path=%s,updated_at=NOW()
                WHERE id = %s
                """

                cursor.execute(update_sql, (
                    self.page_id, line_id, self.layout_direction, self.reading_sequence, char_count, full_text, line_id,
                    section_side,
                    self.page_image_path, pub_text_lines_id))
                print('已更新pub_text_lines')
                return pub_text_lines_id
            else:
                insert_sql = """
                INSERT INTO pub_text_lines (page_id, line_number, text_direction, reading_sequence,char_count, full_text, line_order, section_side,sentence_image_path,created_at, updated_at)
                VALUES (%s, %s, %s, %s,%s, %s, %s, %s,%s,NOW(), NOW())
                """
                cursor.execute(insert_sql, (
                    self.page_id, line_id, self.layout_direction, self.reading_sequence, char_count, full_text, line_id,
                    section_side,
                    self.page_image_path
                ))
                line_db_id = cursor.lastrowid
                print(f"插入新文本行: ID {line_db_id}")
                return line_db_id

        except Error as e:
            print(f"插入文本行错误: {e}")
            raise

    def insert_pub_characters(self, cursor, word_info_line, pub_text_lines_id, section_side):
        """往pub_characters插入字符信息"""
        try:
            for word_item in word_info_line:
                annotation_id = word_item.get('annotation_id')
                char_order = word_item.get('char_order', 0)
                word = word_item.get('word', '')
                word_image_path = word_item.get('word_image_path', '')
                recognition_rate = word_item.get('recognition_rate', 0.0)
                pinyin = word_item.get('pinyin', '')

                # 获取翻译信息
                translation_info = word_item.get('translation_info', {})
                literal_translation = translation_info.get('literal_translation', '')

                # 检查字符是否已存在
                check_sql = "SELECT id FROM pub_characters WHERE annotation_id =%s"
                cursor.execute(check_sql, (annotation_id,))
                result = cursor.fetchone()

                if result:
                    pub_character_id = result[0]
                    print(f"字符已存在: 行 {pub_text_lines_id}, 顺序 {char_order}")
                    # 更新数据
                    update_sql = """
                    UPDATE pub_characters SET line_id=%s, section_side=%s,char_sequence=%s, ocr_char_id=%s, display_text=%s, 
                    original_text=%s, recognition_rate=%s, pinyin=%s, literal_translation=%s, 
                    char_order=%s, updated_at=NOW()
                    WHERE annotation_id = %s
                    """
                    cursor.execute(update_sql, (pub_text_lines_id, section_side, char_order, annotation_id,
                                                word, word, recognition_rate, pinyin, literal_translation, char_order,
                                                pub_character_id))
                    print('已更新pub_characters')
                    continue

                insert_sql = """
                INSERT INTO pub_characters (
                    annotation_id, line_id, section_side,char_sequence, ocr_char_id, display_text, 
                    original_text, recognition_rate, pinyin, literal_translation, 
                    char_order, created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, NOW(), NOW())
                """
                cursor.execute(insert_sql, (
                    annotation_id, pub_text_lines_id, section_side, char_order, annotation_id,
                    word, word, recognition_rate, pinyin, literal_translation, char_order
                ))
                print(f"插入新字符: {word} (顺序: {char_order})")

        except Error as e:
            print(f"插入字符信息错误: {e}")
            raise


def save_book_total_pages(page_idx, book_id):
    try:
        # 获取数据配置
        db_config = get_db_config()

        # 连接数据库
        connection = mysql.connector.connect(**db_config)

        if connection.is_connected():
            cursor = connection.cursor()

            # 检查书籍是否存在
            check_sql = "SELECT id FROM pub_books WHERE id =%s"
            cursor.execute(check_sql, (book_id,))
            result = cursor.fetchone()

            if result:
                update_sql = """
                UPDATE pub_books 
                SET total_pages = %s, updated_at = NOW() 
                WHERE id = %s
                """
                cursor.execute(update_sql, (page_idx, book_id))

            # 提交事务
            connection.commit()

    except Error as e:
        print(f"数据库错误: {e}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
